Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Table of Contents


Index

A

ACID tests (Atomicity, Consistency, Isolation, and Durability), 55-58
ad-hoc, 22, 608
add-ons, see Parallel Query option; Parallel Server option
AdHawk tool, 498
administrator (database administrator), 23, 610
agents, SNMP agents, 76
aggregate functions, 608
AIO, see Asynchronous I/O
alerts, triggers, 470
alias, see synonyms
ALL_CONS_COLUMNS view, 467
ALL_CONSTRAINTS view, 467
ALL_ROWS hint, 479
ALL_TRIGGERS view, 471
ALTER TABLE command, 466
analysis tools, 496
defined, 490
Oracle Mission Control, 496-497
Oracle Trace, 497
parameters, list of, 627-629
summary, 499
third-party tools, 497
AdHawk, 498
DATA-XPERT, 498
DBGENERAL, 498
Patrol, 498-499
Precise/SQL suite, 498
TSreorg, 498
ANALYZE command, 441-442
checking structural integrity, 444
data dictionary statistics, 445-447
gathering chained-row statistics, 444-445
gathering statistics, 442-443
Analyze/SQL tool, 498
AND_EQUAL hint, 481
application development tools, 494, 534
advantages, 534
defined, 490
first-generation tools, 534-535
modern tools, 535
Oracle tools, 494, 536
Developer/2000, 495, 536-544
Power Objects, 495, 544-546
third-party tools, 495, 546
Delphi, 495-496, 547-548
PowerBuilder, 495, 552
ReportSmith, 548-549
SQL Windows, 495, 550-552
tuning automatically generated SQL statements, 535-536
application servers
defined, 557-558
tuning, 559
applications, 17
client, bottlenecks, 523
ConText, 18
designing, 83, 426
clusters, 430
discrete transactions, 435-436
functions, 432-433
hash clusters, 431-432
indexes, 426-429
optimization techniques, 433-434
packages, 432-433
procedures, 432-433
Media Server, 18
Oracle Financial Applications, 17
Oracle Office, 17-18
Oracle WebServer, 18
registering, 417
tuning considerations, 420
analyzing effects of SQL statements, 424-425
analyzing SQL statements, 422-424
problem analysis, 420-421
summary, 425, 591-592
ARCH (Archiver Process), 12, 26-27
architectures
buses, 211-212
memory, 210
virtual memory system, 211
microkernel architecture, 183
networks
hardware components, 566-570
protcols, 570-572
operating systems
NetWare, 178-179
OS/2, 188
UNIX, 192
Windows NT, 183-184
Parallel Server option, 340-343
three-tiered system architecture, 557-558
tuning, 559
two-tiered system architecture, 556
archive logs
batch processing systems, 276
BLOB systems, 332
data warehouses, 314
decision support systems, 295
defined, 26-27, 134-135
financial systems, 372
office systems, 385
OLTP systems, 255
TextServer 3.0 systems, 381
WebServer systems, 389
ARCHIVELOG mode, backup and recovery process, 351
Archiver process (ARCH), 12, 26-27
arrays
disk arrays, 611
processing, 510
Asynchronous I/O (AIO)
defined, 22, 171-172, 608
operating systems, 589
UNIX, 198-200
ATM networks, 570
atomicity, 55
Atomicity, Consistency, Isolation, and Durability (ACID tests), 55-58
AUDIT command, 472-473
audit trails, 472-473
AUDIT_TRAIL parameter, 641
AUTO_MOUNTING parameter, 642

B

B*-Tree index structure, 149
background processes
ARCH (Archiver), 12
CKPT (Checkpoint), 11
fast checkpoints, 26
normal checkpoints, 26
DBWR (Database Writer), 11
defined, 11-12
Dnnn (Dispatcher), 12
LCK (Parallel Server Lock), 12
LGWR (Log Writer), 11
PMON (Process Monitor), 11
RECO (Recovery process), 12
SMON (System Monitor), 12
BACKGROUND_DUMP_DEST parameter, 629
backups
characteristics, 352
cold (offline) backups, 352
data access patterns during backup, 353
goals of backups, 353-354
hot (online) backups, 352-353
loads during backups, 353
cold (offline) backups
defined, 350, 354
hardware considerations, 355-356
physical data access, 354-355
software considerations, 355-356
design considerations, summary, 358
enhancements, 359
CPU, 359
I/O, 359-360
networks, 360
segmenting backups, 360-362
summary, 361-362
testing database, 362
testing operating system, 363-365
verifying performance, 362-365
exports, 350
full backups, 350
hot (online) backups
defined, 350, 355-356
hardware considerations, 357-358
physical data access, 356-357
software considerations, 357-358
offline backups, 350
online tablespace backups, defined, 350
processes, 351
recovery process, 351-352
software testing, 364-365
tuning considerations, 358-359
bandwidth
buses, 212
defined, 608
network design, 576-577
batch processing systems
archive logs, 276
block
buffers, 274
size, 277
characteristics, 266-267
data access patterns, 267
goals of optimally tuned systems, 268-269
loads, 267-268
summary, 269
checkpoints, 276
clusters, 277
comparisons to OLTP systems, 265
contention, latch contention, 275
defined, 27, 608
design considerations, 270
direct write sorts, 277
enhancements, 590
benchmarks, 282-283
CPU, 279
hardware, 279-281
high-speed compression, 280-281
high-speed tape, 280-281
I/O, 279-280
list of, 277
networks, 280
Parallel Query option, 277-278
Parallel Server option, 278
summary, 281
testing database, 281
testing operating system, 282
verifying performance, 281-283
hardware considerations, 274
hash clusters, 277
indexes, 277
library cache, 275
performance criteria of system, 38
checklist, 39
physical data layouts, 270-271
disk arrays, 272-273
traditional disks, 271-272
segments, rollback segments, 275
tuning considerations
Oracle, 274-276
server operating systems, 276-277
benchmarks
batch processing systems, 282-283
BLOB systems, 336-337
custom benchmarks, 70
defined, 51-52
writing benchmarks, 70-72
data warehouses, 320-321
decision support systems, 301-302
defined, 51-52
OLTP systems, 262
publications, 69
standard benchmarks
defined, 51-53
TPC (Transaction Processing Performance Council), 53- 57
testing, 69
UNIX features, 200-203
Binary Large Objects, see BLOB systems
binding variables, 401
bits, 29
BLANK_TRIMMING parameter, 629
BLOB systems (Binary Large Objects)
archive logs, 332
block
buffers, 332
size, 333
checkpoints, 332
characteristics, 324
data access patterns, 324
goals of optimally tuned systems, 325-326
loads, 324-325
summary, 326-327
clusters, 333
contention, latch contention, 332
defined, 28, 323-324, 608
design considerations, 327
enhancements, 333, 590
benchmarks, 336-337
CPU, 334
hardware, 334-335
I/O, 334-335
networks, 335
Parallel Query option, 333
summary, 335
testing database, 336
testing server operating system, 336
verifying performance, 335-337
hardware considerations, 331
indexes, 333
library cache, 332
mutiblock reads, 332
physical data layout, 328
disk arrays, 329-331
traditional disks, 328-329
segments, rollback segments, 332
tuning considerations
Oracle, 332
server operating systems, 333
blocks
buffers
batch operating systems, 274
BLOB systems, 332
cache, 583
data warehouses, 313
decision support systems, 294
defined, 22
financial systems, 371
office systems, 384
OLTP systems, 254
replicated systems, 376
TextServer 3.0 systems, 381
WebServer systems, 388
defined, 22, 608
indexes, 149
multiblock reads or writes, 152-153
size, 584
batch processing systems, 277
BLOB systems, 333
changing, 140-141
data warehouses, 315
decision support systems, 296
financial systems, 372
OLTP systems, 256
testing disk block size in backup process, 363
testing tape drives for backup process, 363
TextServer 3.0 systems, 381
WebServer systems, 389
body
functions, 454
packages, 458
procedures, 453
bottlenecks
clients, performance, 522
application, 523
hardware, 524
network, 523
presentation, 524
defined, 92
finding, 92
removing, 93
branch blocks, indexes, 149
bridges, networks, 577
buffer
buffers
block buffers, 608
batch operating systems, 274
BLOB systems, 332
cache, 583
data warehouses, 313
decision support systems, 294
defined, 22
financial systems, 371
office systems, 384
OLTP systems, 254
replicated systems, 376
TextServer 3.0 systems, 381
WebServer systems, 388
cache, 9, 22, 608
cache-hits ratio, 107-108
SQL statement, 597
tuning, 107-108
UNIX operating systems, 193
clean buffers, 22, 608
defined, 22, 608
dirty buffers, 22, 608
disk drive, 215
redo log buffer, 10, 608
contention, 600-601
latch contention, 131-132
buses
architecture, 211-212
bandwidth, 212, 608
defined, 211
business models, 27-28
see also financial systems
bytes, 29

C

cache
buffer cache, 9, 22, 583
cache-hits ratio, 107-108
SQL statement, 597
tuning, 107-108
UNIX operating systems, 193
CPU cache, 206-207, 210
data dictionary cache, 582
SQL statement, 596-597
defined, 22, 608
disk arrays
controller caches, 228
read/write caches, 229
write caches, 228-229
hits, 609
increasing in library cache, 100-102
ratio in buffer cache, 107-108
library cache, 450-452, 582
batch processing systems, 275
BLOB systems, 332
decision support systems, 295
financial systems, 371
hits, 451
misses, 451
office systems, 384
replicated systems, 376
SQL statement, 596
stored functions and procedures, 456-457
TextServer 3.0 systems, 381
WebServer systems, 388
cache affinity, 174
batch processing systems, 276
BLOB systems, 333
data warehouses, 314
decision support systems, 296
OLTP systems, 255
operating systems, 589
UNIX
disabling preemptive scheduling, 202
summary of tuning guidelines, 202-203
CACHE hint, 485
cache miss, shared pool, 101
CACHE_SIZE_THRESHOLD parameter, 347, 639
calls, recursive calls, 118
Cartesian products, 505-506, 609
CD-ROM, SQL scripts on, 646-648
Central Processing Unit, see CPU
chaining rows, 117-118
gathering chained-row statistics, 444-445
SQL statement, 599
CHAR data type, 510
CHECK constraint, 465
CHECKPOINT_PROCESS parameter, 629
checkpoints, 11, 26, 133-134
batch processing systems, 276
BLOB systems, 332
data warehouses, 314
decision support systems, 295
defined, 26
fast checkpoints, 26, 134
financial systems, 372
normal checkpoints, 26, 133
office systems, 385
OLTP systems, 255
TextServer 3.0 systems, 381
WebServer systems, 389
checksums, 23, 609
CHOOSE hint, 479
CISC processors (Complex Instruction Set Computer), 207-208
CKPT, see checkpoints
classes, Oracle Power Objects, 545-546
classroom training, 19
CLEANUP_ROLLBACK_ENTRIES parameter, 630
client processes, 10
client/server systems
defined, 519-520
performance criteria, 32-33
checklist, 35-36
client component, 33
network, 35
server component, 33-35
transactions, running, 32
clients
bottlenecks, performance, 522
application, 523
hardware, 524
network, 523
presentation, 524
computing models, 520
three-tiered system, 521-522
two-tiered system, 520-521
defined, 32, 516
development of
client/server model, 519-520
GUI/Server model, 517-519
network computing model, 517
traditional computing model, 516-517
performance criteria, 33
tuning considerations, 85
summary, 592
CLOSE_CACHED_OPEN_ CURSORS parameter, 630
CLUSTER hint, 481
cluster key, 609
clusters, 584
advantages, 141-143
batch processing systems, 277
BLOB systems, 333
computers, 609
creating, 430
data warehouses, 315
decision support systems, 296
defined, 6
design stage, 83
disadvantages, 142-143
financial systems, 372
index clusters, 141, 427, 609
OLTP systems, 256
Parallel Server option, 340
tables, 609
TextServer 3.0 systems, 381
views, data in, 446
WebServer systems, 389
see also hash clusters
cold (offline) backups
defined, 350, 352-354
hardware/software considerations, 355-356
physical data access, 354-355
cold data, 609
cold databases, 609
collisions, 609
columns
indexes, choosing columns to index, 428
indexing, data selection guidelines, 151
tables, 6
views, data in, 447
commands
ALTER TABLE, 466
ANALYZE, 441-442
checking structural integrity, 444
data dictionary statistics, 445-447
gathering chained-row statistics, 444-445
gathering statistics, 442-443
AUDIT, 472-473
CREATE SEQUENCE, parameters, 502-503
CREATE TABLE, 466
CREATE TRIGGER, 469
DDL (Data Definition Language), 23, 610
DML (Data Manipulation Language), 23, 611
EXPLAIN PLAN
analyzing SQL statement execution, 424
defined, 394-395, 404, 414
extracting results of, 416
initialization, 414-415
invoking, 415-416
NOAUDIT, 472-473
COMMIT_POINT_STRENGTH parameter, 638
COMPATABLE parameter, 630
COMPATABLE_NO_ RECOVERY parameter, 630
Complex Instruction Set Computer (CISC processors), 207-208
complex statements, 609
component queries, 609
composite indexes, 149
advantages, 151
creating, 428-429
defined, 427
compound queries, 609
compression, high- performance com- pression, 365
computing
client/server models, 519-520
three-tiered system, 521-522
two-tiered system, 520-521
development of, 516-520
GUI/Server model, 517-519
network computing model, 517
traditional computing model, 516-517
concurrency, 23, 610
configuring disk array systems, 240
connectivity, 43
consistency, 55
consistent mode, 610
consistent read, 610
constraints, 466
CHECK, 465
defined, 610
FOREIGN, 465
implementing
at table creation, 466
at table modification, 466-467
integrity constraints, list of, 465
NOT NULL, 465
PRIMARY, 465
summary, 469
UNIQUE, 465
viewing, 467-469
consulting services, 18
contention
defined, 23, 610
disk contention, 109-110
identifying problems, 110-111
isolating sequential I/Os, 112
separating data from index, 116
striping random I/Os, 112-116
free list contention, 136-137
SQL statement, 602
latch contention, 130-131, 314
batch processing systems, 275
BLOB systems, 332
data warehouses, 314
decision support systems, 295
financial systems, 371
office systems, 385
OLTP systems, 255
redo log buffer, 131-132
replicated systems, 376
SQL statement, 601
TextServer 3.0 systems, 381
WebServer systems, 389
redo log buffer contention, 130-131
rollback contention, 123
financial systems, 371
office systems, 385
OLTP systems, 254
replicated systems, 376
SQL statement, 599-600
WebServer systems, 389
SQL statement, 600-601
ConText, 18
control files, 5
CONTROL_FILES parameter, 630
controller, 227
caches, 228
fiber optic networks, 569
read/write caches, 229
write caches, 228
conversions, 28
binary storage units, 29-30
powers of 10, 29
copying databases, see replicated systems
core
OS/2, 188
Windows NT, 183
cost-based optimization method
ANALYZE command, 441-442
checking structural integrity, 444
data dictionary statistics, 445-447
gathering chained-row statistics, 444-445
gathering statistics, 442-443
choosing, 438-439
defined, 434, 441, 610
hints, 447
count parameter, SQL Trace, 409
CPU (Central Processing Unit), 206
cache, 206-207, 210
CISC processors (Complex Instruction Set Computer), 207-208
defined, 206-207
enhancements
backup process, 359
batch processing system, 279
BLOB system , 334
data warehouse, 317
decision support system, 298-299
OLTP, 257-258
replicated system, 377
multiprocessor systems, 209
MPP (Massively Parallel Processor), 209-210
SMP (Symmetric Multiprocessor), 209
RISC processors (Reduced Instruction Set Computer), 208
cpu parameter, SQL Trace, 409
CREATE SEQUENCE command, parameters, 502-503
CREATE TABLE command, 466
CREATE TRIGGER command, 469
current mode, 610
current parameter, SQL Trace, 409
current read, 610
cursor, 23, 610
CURSOR SPACE FOR TIME parameter, 254
custom benchmarks
defined, 51-52
writing benchmarks, 70-71
analysis, 72
design, 71
implementation, 71-72
Customer-Demographics transactions, OLTP, 65
Customer-Inquiry transactions, OLTP, 65
Customer-Status transactions, OLTP, 65
cylinders (platters), 215

D

data access patterns
backup process, 353
batch processing systems, 267
BLOB systems, 324
consistent read, 610
data warehouses, 305-306
decision support systems, 287-288
OLTP systems, 246-247
data blocks, 8
see also extents; segments
Data Definition Language, see DDL
data dictionary
cache, 103-104, 582
defined, 5, 23, 610
data files
defined, 5
logical layer, 5-6
data generation, triggers, 469
data integrity, see integrity
Data Manipulation Language, see DML
data segments, 8
data transfer rate, 217-218
data validation, triggers, 469
data warehouses
archive logs, 314
block
buffers, 313
size, 315
characteristics, 304-305
data access patterns, 305-308
loads, 306-307
summary, 307-308
checkpoints, 314
clusters, 315
contention, latch contention, 314
defined, 28, 610
design considerations, 308
fault tolerance, 311
hardware, 312
physical data layout, 308-311
direct-write sorts, 315
enhancements, 590
benchmarks, 320-321
CPU, 317
hardware, 317-319
I/O, 317-318
list of, 315
networks, 319
Parallel Query option, 316
Parallel Server option, 316-317
summary, 319
testing database, 320
testing operating system, 320
verifying performance, 319-321
fault tolerance considerations, 311
hardware considerations, 312
hash clusters, 315
indexes, 315
library cache, 313
multiblock reads, 313
physical data layout
disk arrays, 310-311
traditional disks, 309-310
segments, rollback segments, 313
tuning considerations
Oracle, 312-314
server operating systems, 314-315
DATA-XPERT tool, 498
database administrator (DBA), 610
database design tools
defined, 490
Designer/2000, 490-492
generators, 492
Process Modeller, 491
Systems Designer, 491-492
Systems Modeller, 491
third-party tools, 492-493
ERwin/ERX, 493
S-Designor, 493
SQL Coder, 493-494
database replication, see replicated systems
Database Trigger Editor (Procedure Builder tool), 542
Database Writer (DBWR), 11
databases
blocks, 608
cold databases, 609
design stage, layout considerations, 82
hot databases, 611
logical layer
data blocks, 8
data dictionary, 5
data files, 5-6
defined, 5
extents, 8
schemas, 25
segments, 7
tablespaces, 5-6
offline databases, 613
online databases, 613
physical layer
control files, 5
data files, 5
defined, 4
redo log files, 5
schema
defined, 6-7
objects, 6
data dictionary cache, SQL statement, 596-597
DB_BLOCK_BUFFERS parameter, 620
DB_BLOCK_CHECKPOINT_ BATCH parameter, 620-621
DB_BLOCK_CHECKSUM parameter, 628
DB_BLOCK_LRU_EXTENDED_ STATISTICS para- meter, 628
DB_BLOCK_LRU_STATISTICS parameter, 628
DB_DOMAIN parameter, 630
DB_FILE_MULTIBLOCK_ READ_COUNT para-meter, 621
DB_FILE_SIMULTANEOUS_ WRITES parameter, 621
DB_FILES parameter, 631
DB_LOG_CHECKSUM parameter, 628
DB_MOUNT_MODE parameter, 642
DB_NAME parameter, 631
DBA (Database Adminstrator), 23, 610
DBA_CONS_COLUMNS view, 467
DBA_CONSTRAINTS view, 467
DBA_TRIGGERS view, 471
DBGENERAL tool, 498
DBLINK_ENCRYPT_LOGIN parameter, 631
DBWR (Database Writer), 11
DDL (Data Definition Language) commands, 23, 610
deadlocks, 510, 611
decision support systems
archive logs, 295
block size, 296
characteristics, 287
data access patterns, 287-288
goals of optimally tuned systems, 289
loads, 288-289
summary, 289-290
checkpoint, 295
clusters, 296
contention, latch contention, 295
defined, 28, 611
design considerations, 290
physical data layout, 291-293
direct write sorts, 296
enhancements, 296-297, 590
benchmarks, 301-302
CPU, 298-299
hardware, 298-300
I/O, 299-300
networks, 300
Parallel Query option, 297
Parallel Server option, 297-298
summary, 300
testing database, 301
testing operating system, 301
verifying performance, 300-302
hardware considerations, 294
hash clusters, 296
indexes, 296
library cache, 295
multiblock reads, 295
physical data layout, 291
disk arrays, 292-293
traditional disks, 291-292
segments, rollback segments, 295
tuning considerations
Oracle, 294-295
server operating system, 295-296
declarations
functions, 454
packages, 458
procedures, 453
deferred frames, 611
DELETE statement, 611
displaying execution plan with EXPLAIN PLAN command, 414-416
Delivery transaction (TPC-C benchmark), 62
Delphi, 495-496, 547
Delphi Forms Developer, 547-548
design considerations, 82
applications, 83, 426
clusters, 430
discrete transactions, 435-436
functions, 432-433
hash clusters, 431-432
indexes, 426-429
optimization techniques, 433-434
packages, 432-433
procedures, 432-433
backup process, 354
cold (offline) backups, 354-355
hot (online) backups, 355-358
summary, 358
batch processing systems
hardware, 274
physical data layout, 270-273
BLOB systems, 327
hardware, 331
physical data layout, 328-331
clusters, 83
data warehouses, 308
database layout, 82
decision support systems, 290
hardware, 294
distributed systems, 378-379
financial systems, 369-371
hardware selection, 83-84
indexes, 83
networks, 84, 576
bandwidth, 576-577
bridges, 577
hubs, 577
routers, 577
segmenting networks, 577
office systems, 384
Parallel Server option, 343
goals, 343-345
physical data layout, 308-309
replicated systems, 375-377
goals of optimally tuned systems, 375
systems, list of, 590-591
TextServer 3.0 systems, 380-381
WebServer systems, 387-388
goals of optimally tuned systems, 387
Designer/2000, 16, 82, 490-492
generators, 492
Process Modeller, 491
Systems Designer, 491-492
Systems Modeller, 491
Developer/2000, 16, 82, 495
features, 536-544
Forms Designer tool, 536-538
Graphics Designer tool, 537, 540-542
Procedure Builder tool, 537, 542-544
Reports Designer tool, 536, 538-540
development tools, see application development tools
device drivers, 611
Dircect FS interface, 171
direct I/O, see synchronous I/O
Direct Write Sort option (Parallel Query option), 158
direct write sorts, 143, 158
batch processing systems, 277
data warehouses, 315
decision support systems, 296
disabling
SQL Trace
for an instance, 406
per-session basis, 405-406
SQL Trace command, 410
discrete transactions, 435-436
DISCRETE_TRANSACTIONS_ ENABLED parameter, 621
disk arrays
advantages, 226
batch processing systems, 272-273
BLOB systems, 329-331
configuring RAID, 235-236
distributing random I/Os, 237-238
isolating sequential I/Os, 236-237
sizing logical volume, 238-239
configuring systems, 240
data warehouses, 310-311
decision support systems, 292-293
defined, 24, 225-226, 611
fault-tolerance concerns, 233
Full Data Protection mode, 233-234
No Data Protection mode, 233
Partial Data Protection mode, 233-235
summary, 235
hardware arrays
controller, 227-228
controller caches, 228
read/write caches, 229
write caches, 228-229
hot-swappable disk drives, 228
I/O rates, 234
logical disks, defined, 24
OLTP systems, 252-253
operations, 226
RAID, 229
comparing RAID levels, 240-241
RAID-0, 230
RAID-1, 230-231
RAID-2, 231
RAID-3, 231
RAID-4, 232
RAID-5, 232
summarizing RAID levels, 232
software arrays, 227
disk bound systems, 108
disk I/O, 108-109
contention, 109-110
checkpoints, 133-134
free list contention, 136-137
identifying problems, 110-111
isolating sequential I/Os, 112
latch contention, 130-132
rollback segments, 123-130
separating data from indexes, 116
striping random I/Os, 112-116
data transfer rate, 217-218
disk operation, 214-215
interfaces, list of, 171
queue time, 218
random I/Os, 220
rotational latency, 217
averages, 219
seek time, 216-217
averages, 219
sequential I/Os, 220-222
SQL statement, 597-598
disk parameter, SQL Trace, 409
disks
block size, testing in backup process, 363
defined, 214
drive, 214
buffer, 215
cylinder, 215
data transfer rate, 217-218
hot-swappable disk drives, 228
platters, 214
queue time, 218
rotational latency, 217
tracks, 214
fragmentation, 144-146
hot spots, 108
logical disks, 24, 612
storage capacity, 98
Dispatcher processes (Dnnn process), 12
Distributed Lock Manager, see DLM
distributed option
defined, 14
parameters, list of, 638-639
distributed systems
characteristics, 378
defined, 368, 377-378
design considerations, 378-379
summary of, 379
transactions, 378
tuning considerations, 378-379
DISTRIBUTED_LOCK_ TIMEOUT parameter, 638
DISTRIBUTED_ RECOVERY_CONNECTION_ HOLD_TIME para- meter, 638
DISTRIBUTED_ TRANSACTIONS parameter, 639
DLM (Distributed Lock Manager), 162
Parallel Server option, 341-342
DML (Data Manipulation Language) commands, 23, 611
DML_LOCKS parameter, 622
Dnnn process (Dispatcher processes), 12
drivers, device drivers, 611
drives, disk drives
buffer, 215
cylinder, 215
data transfer rate, 217-218
defined, 214
hot-swappable disk drives, 228
platters, 214
queue time, 218
rotational latency, 217
testing block size for backup process, 363
tracks, 214
DSS, see decision support systems
durability, 56
dynamic performance tables
buffer cache statistics, 107
defined, 24, 611
V$FILESTAT, disk access information, 597
V$LATCH, latch contention data, 601
V$ROLLSTAT, dynamic rollback growth data, 600
V$ROLLSTAT table, rollback segment data, 129
V$SYSSTAT, recursive calls data, 599
V$WAITSTAT
rollback contention data, 599
rollback segment data, 127
views, table of, 74-75

E

educational services, 19
elapsed parameter, SQL Trace, 409
elapsed time, SQL Trace, 412
enabling
SQL Trace
for an instance, 406
per-session basis, 405
SQL Trace command, 409
enhancements, 583
block size, 584
clusters, 584
fragmentation reductions, 584-585
hash clusters, 585
indexes, 585
multiblock reads and writes, 586
Parallel Query option, 586-587
Parallel Server option, 587-588
spin counts, 588
ENQUEUE_RESOURCES parameter, 631
equijoins, 505, 611
errors, hints, 477-478
ERwin/ERX tool, 493
Ethernet, 566-568, 611
packets, 567
Etherplex board, OLTP enhancements, 259
EVENT parameter, 628
events, logging triggers, 469
exception handlers, 455
EXCEPTION statement, 455
Exclusive Lock mode, Parallel Server option, 342
executing SQL statements, 401
execution plans, displaying, 414-416
EXPLAIN PLAN command
analyzing SQL statement execution, 424
defined, 77, 394-395, 404, 414
extracting results of, 416
initialization, 414-415
invoking, 415-416
explicit locking, 509
exports, 350
extents, 8, 611
see also data blocks; segments

F

fast checkpoints, 26, 134
fault-tolerance
comparisons for disk array RAID levels, 240-241
data warehouses, 311
disk arrays, 233
Full Data Protection mode, 233-234
No Data Protection mode, 233
Partial Data Protection mode, 233-235
summary, 235
goals, 43
Parallel Server option, 340
FDR (Full Disclosure Report), TPC benchmarks, 56-57
fiber optic networks (FDDI networks), 569-570
Fibre Channel networks, 570
file systems, UNIX, 198
files
logical layer, data files, 5-6
physical layer, 5
redo log files, 614
financial systems, 17
archive logs, 372
block
buffers, 371
size, 372
characteristics, 369
checkpoints, 372
clusters, 372
contention
latch contention, 371
rollback contention, 371
defined, 368
design considerations, 369-371
goals for optimally tuned systems, 369
enhancements, 372-373
hash clusters, 372
indexes, 372
library cache, 371
reads and writes, 372
Parallel Query option, 372
segments, rollback segments, 371
spin counts, 371
summary of, 373
tuning considerations, 371-372
FIRST_ROWS hint, 479-480
FIXED_DATE parameter, 628
flowcharts
Oracle optimizer, 605
problem-solving methodology, 604
SQL statement processing, 605
user-transaction profile, 605
FOREIGN constraint, 465
foreign keys, 611
Forms Designer tool
Designer/2000, 536-538
Oracle Power Objects, 544-545
Forms Developer (Delphi), 547-548
Forms Generator tool, 492
formulas, checksums, 23, 609
fragmentation, 144-146
reducing, 584-585
frames
deferred frames, 611
defined, 613
free list contention, 136-137
SQL statement, 602
front-end computer, see clients
full backups, 350
Full Data Protection mode (fault-tolerance), 234
Full Disclosure Report (FDR), 67-69
TPC benchmarks, 56-57
FULL hint, 481
functions, 432-433
aggregate functions, 608
body, 454
comparisons to, 452
declarations, 454
defined, 24, 452-454, 611
EXCEPTION, 455
packages, 24
parameters, 454
PL/SQL language, 454-455
RDBMS_OUTPUT package, 456
RETURN, 454-455
return values, 454
stored functions
creating, 456-457
defined, 452
properties, 452
replacing, 457
syntax, 453
see also procedures

G

GB (gigabyte), 30
GC_DB_LOCKS parameter, 347, 639
GC_FILES_TO_LOCKS parameter, 347, 640
GC_LCK_PROCS parameter, 347, 640
GC_ROLLBACK_LOCKS parameter, 347, 640
GC_ROLLBACK_SEGMENTS parameter, 640
GC_SAVE_ROLLBACK_LOCKS parameter, 640
GC_SEGMENTS parameter, 640
GC_TABLESPACES parameter, 640
generating data, triggers, 469
generators, 492
gigabyte (GB), 30
GLOBAL_NAMES parameter, 632
goals (tuning), 42
backup process, 353-354
connectivity, 43
design goals
financial systems, 369
Parallel Server option, 343-345
replicated systems, 375
WebServer systems, 387
fault tolerance, 43
load time, 44
response time, 42-43
setting goals for optimal performance, 48-50
throughput, 42
tuning considerations
batch processing systems, 268-269
BLOB systems, 325-326
data warehouses, 307-308
decision support systems, 289
OLTP systems, 248-249
operating systems, 589
Graphics Designer tool (Designer/2000), 537, 540-542

H

HAL (Hardware Abstraction Layer), 184
hardware
backup process
cold (offline) backups, 355
hot (online) backups, 357-358
batch processing systems, 274
enhancements, 279-281
BLOB systems, 331
enhancements, 334
bottlenecks
client, 524
finding, 93
buying considerations, 83-84
CPU (Central Processing Unit), 206-207
data warehouses, 312
enhancements, 317-319
decision support systems, 294
enhancements, 298-300
network components, 566
Ethernet networks, 566-568
fiber optic networks, 569-570
new technologies, 570
Token Ring networks, 568-569
OLTP systems, 253, 257
CPU enhancements, 257-258
Etherplex board, 259
I/O enhancements, 258-259
network enhancements, 259
striping, 114-115
tuning, 531-532
resources, 94
Hardware Abstraction Layer (HAL), 184
hardware disk arrays, controller, 227-228
caches, 228
read/write caches, 229
write caches, 228-229
hash clusters, 146-147, 585
advantages, 147-148
batch processing systems, 277
creating, 431-432
data warehouses, 315
decision support systems, 296
disadvantages, 147-148
financial systems, 372
OLTP systems, 256
TextServer 3.0 systems, 381
WebServer systems, 389
see also clusters
HASH hint, 482
hierarchy
databases
logical layer, 5-8
physical layer, 4-5
instance, 8
memory structure, 8-10
processes, 10-13
transactions, 12-13
high-performance compression, backup process, 365
high-speed compression, batch processing systems, 280-281
high-speed tape, batch processing systems, 280-281
hints
access methods, 481
AND_EQUAL hint, 481
CLUSTER hint, 481
FULL hint, 481
HASH hint, 482
INDEX hint, 482
INDEX_ASC hint, 483
INDEX_DESC hint, 483
ORDERED hint, 484
ROWID hint, 483
USE_CONCAT hint, 483-484
USE_MERGE hint, 484
USE_NL hint, 485
advantages, 476
errors, 477-478
examples, 477-478
implementing, 476-478
multiple, 478
optimization techniques, 447-478
ALL_ROWS hint, 479
CHOOSE hint, 479
FIRST_ROWS hint, 479-480
RULE hint, 480-481
Parallel Query option, 485
CACHE hint, 485
NOCACHE hint, 486
NOPARALLEL hint, 486
PARALLEL hint, 485-486
PUSH_SUBQ hint, 486
syntax, 477
host-based systems, see terminal-based systems
hot (online) backups, 350-353, 355-356
hardware considerations, 357-358
physical data access, 356
isolating networks, 357
isolating tablespaces, 356
temporary backup space, 357
software considerations, 357-358
hot data, 611
hot databases, 611
hot spots, 108
hot-swappable disk drives, 228
HPPI (High Performance Parallel Interface) networks, 570
hubs, network design considerations, 577

I

I/O (Input/Output)
asynchronous I/O, 171-172
backup process, enhancements, 359-360
batch processing systems, 276
enhancements, 279-280
BLOB systems, 333
enhancements, 334-335
checkpoints, 133-134
contention, 109-110
free list contention, 136-137
identifying contention problems, 110-111
latch contention, 130-132
data transfer rate, 217-218
data warehouses, 314
enhancements, 317-318
decision support systems, 296
enhancements, 299-300
defined, 108-109, 170-171, 612
Dircect FS interface, 171
disk arrays, 234
interfaces, list of, 171
OLTP systems, 255
enhancements, 258-259
operating systems, 589
NetWare, 182-183
OS/2, 190-191
UNIX, 197-200
Windows NT, 186-187, 528
operation, 214-215
queue time, 218
random I/O
defined, 24, 220, 598, 614
disk rates, 598
distributing in disk arrays, 237-238
spreading out, 590, 598
striping data, 112-116
raw devices, 171
reducing overhead, 117
chaining rows, 117-118
checking for recursive calls, 118-119
dynamic extensions, 118-119
migrating rows, 117-118
PCTFREE command option, 119-122
PCTUSED command option, 119-122
techniques, 122
replicated system enhancement, 377
rotational latency, 217-219
seek time, 216-217, 219
separating data from indexes, 116
sequential I/O
defined, 25, 111, 220-222, 598, 615
disk rates, 598
isolating, 112, 590, 598
isolating for disk arrays, 236-237
SQL statement, 597-598
synchronous I/O, 171-172
tuning considerations, 590
I/O bound systems, 108
IFILE parameter, 632
INDEX hint, 482
index segments, 8
INDEX_ASC hint, 483
INDEX_DESC hint, 483
indexes, 585
avoiding, 429
B*-Tree structure, 149
batch processing systems, 277
BLOB systems, 333
blocks, 149
choosing
columns to index, 428
tables to index, 427-428
cluster indexes
advantages, 141-165
creating, 430
defined, 141, 427, 584
disadvantages, 142-143
see also hash clusters
composite indexes
advantages, 151
creating, 428-429
defined, 149, 427
creating, 149-150, 426-429
parallel index creation, 159
data selection guidelines, 150-152
data warehouses, 315
decision support systems, 296
defined, 6, 148-152, 612
design stage, 83
financial systems, 372
nonunique indexes, defined, 149, 427
OLTP systems, 256
separating data from, 116
TextServer 3.0 system, 381
types, 149
unique indexes, 149, 427
INIT_SQL_FILES parameter, 632
initialization parameters, 612
initializing
EXPLAIN PLAN command, 414-415
SQL Trace parameters, 404-405
Input/Output, see I/O
INSERT statement
defined, 612
execution plan, displaying with EXPLAIN PLAN command, 414-416
Inspect/SQL tool, 498
instance
archive logs, 134-135
buffer cache, SQL statement, 597
chained rows, SQL statement, 599
checkpoints, 133-134
data dictionary cache, SQL statement, 596-597
defined, 4, 8, 612
disk I/O
SQL statement, 597-598
storing data, 98
free list contention, 136-137
SQL statement, 602
latch contention, 130-131
SQL statement, 601
library cache, SQL statement, 596
memory
buffer cache, 107-108
operating system, 99
private PL/SQL area, 100
private SQL area, 100
shared pool, 100-106
sorts, 135-136
storing data, 98
migrated rows, SQL statement, 599
processes
background processes, 11-12
defined, 10
server (shadow) processes, 11
user (client) processes, 10
recursive calls, SQL statement, 599
redo log buffer contention, 130-131, 600-601
latch contention, 131-132
rollback contention, SQL statement, 599-600
rollback segments, 123-126
avoiding dynamic growth, 129
contention, 123-130
dynamic growth of, 600
extent size and number, 128-129
number of, 127-128
optimizing, 130
size, 128
shared memory structure, 8
PGA (Program Global Area), 10
SGA (System Global Area), 9-10
sorts, SQL statement, 601-602
transactions, 25
INSTANCE_NUMBER parameter, 641
integrity, 462
constraints, 466
implementing at table creation, 466
implementing at table modification, 466-467
list of, 465
summary, 469
viewing, 467-469
referential integrity
defined, 462, 614
tables, 463-464
serial reads, 473
see also triggers
interfaces
I/O, list of interfaces, 171
raw device interface, UNIX, 198
IP/SQL statements, procedures, 24
ISM (Intimate Shared Memory), UNIX operating systems, 201
isolation, 56

J

JOB_QUEUE_INTERVAL parameter, 632
JOB_QUEUE_KEEP_ CONNECTIONS parameter, 632
JOB_QUEUE_PROCESSES parameter, 632
joins
Cartesian products, 505, 609
defined, 505, 612
equijoins, 505, 611
join conditions, 612
nonequijoins, 613
operations, hints, 484-485
orders, hints, 484
outer joins, 505, 507, 613
self joins, 505-506, 615
summary, 507
tuning for response time or throughput, 507

K

KB (kilobyte), 30
kernels
OS/2, 188
Windows NT, 183
keys
foreign keys, 611
primary keys 614
keywords, triggers, 470

L

LABEL_CACHE_SIZE parameter, 642
LANs (Local Area Networks), 612
latch contention, 130-131
batch processing systems, 275
BLOB systems, 332
decision support systems, 295
financial systems, 371
office systems, 385
OLTP systems, 255
redo log buffer contention, 130-131
latch contention, 131-132
replicated systems, 376
SQL statement, 601
TextServer 3.0 systems, 381
WebServer systems, 389
latency, 214
rotational latency, 217
averages, 219
layers
HAL (Hardware Abstraction Layer), 611
logical layer
data blocks, 8
data dictionary, 5
data files, 5-6
defined, 5
extents, 8
schemas, 25
segments, 7
tablespaces, 5-6
physical layer
control files, 5
data files, 5
defined, 4
redo log files, 5
layouts, 82
batch processing systems, 270-271
disk arrays, 272-273
traditional disks, 271-272
BLOB system, 328
disk arrays, 329-331
traditional disks, 328-329
data warehouses, 308-309
disk arrays, 310-311
traditional disks, 309-310
decision support system, 291
disk arrays, 292-293
traditional disks, 291-292
OLTP system, 250
disk arrays, 252-253
traditional disks, 250-252
LCK (Parallel Server Lock processes), 12
leaf blocks, indexes, 149
Level 1 cache, 206
Level 2 cache, 206
LGWR process (Log Writer), 11
library cache, 100-102, 450-452, 582
batch processing systems, 275
BLOB systems, 332
cache hits, 451
increasing, 100-102
data warehouses, 313
decision support systems, 295
financial systems, 371
misses, 451
office systems, 384
OLTP systems, 254
replicated systems, 376
SQL statement, 596
SQL Trace, statistics on library cache, 413
stored functions or procedures
creating, 456-457
replacing, 457
TextServer 3.0 systems, 381
WebServer systems, 388
LICENSE_MAX_SESSIONS parameter, 632
LICENSE_MAX_USERS parameter, 633
LICENSE_SESSIONS_ WARNING parameter, 633
lightweight process, 612
see also processes; threads
load balancing, disabling in UNIX, 202
load time, goals, 44
loads
backup process, 353
batch processing systems, 267-268
BLOB systems, 324-325
data warehouses, 306-307
decision support systems, 288-289
OLTP systems, 247-248
parallel loading, 159
Local Area Networks, see LANs
local transactions, distributed systems, 378
locking
deadlocks, 510
defined, 508
DLM (Distributed Lock Manager), 162
explicit locking, 509
Parallel Server option, 342
DLM (Distributed Lock Manager), 341
PCM (Parallel Cache Management), 162, 342-343
row-level locking, 508
SELECT…FOR UPDATE statement, 509
serial reads, 508-509
summary, 510
table-level locking, 508
log files, redo log files, 614
Log Writer, see LGWR
LOG_ARCHIVE_BUFFER_SIZE parameter, 622
LOG_ARCHIVE_BUFFERS parameter, 622
LOG_ARCHIVE_DEST parameter, 633
LOG_ARCHIVE_FORMAT parameter, 633
LOG_ARCHIVE_START parameter, 634
LOG_BUFFER parameter, 622
LOG_CHECKPOINT_ INTERVAL parameter, 622
LOG_CHECKPOINT_TIMEOUT parameter, 623
LOG_CHECKPOINTS_TO_ ALERT parameter, 634
LOG_FILES parameter, 634
LOG_SIMULTANEOUS_ COPIES parameter, 623
LOG_SMALL_ENTRY_MAX_ SIZE parameter, 623
logging
archive logs, 134-135
batch processing systems, 276
BLOB systems, 332
data warehouses, 314
decision support systems, 295
financial systems, 372
office systems, 385
TextServer 3.0 systems, 381
WebServer systems, 389
events, triggers, 469
redo log files, 26-27
logical disks, 24, 612
logical layer
data blocks, 8
data dictionary, 5
defined, 5
extents, 8
schema, 25
objects, 25
segments, 7
tablespaces
data files, 5-6
defined, 5-6
SYSTEM tablespace, 5-6
logical volumes
defined, 226
sizing, 238-239
stripes, 226
logs, archive logs in OLTP systems, 255

M

main memory, 612
Massively Parallel Processor, see MPP
master/slave architecture, NetWare, 179
MAX_COMMIT_ PROPAGATION_DELAY parameter, 641
MAX_DUMP_FILE_SIZE parameter, 405, 634
MAX_ENABLED_ROLES parameter, 634
MAX_ROLLBACK_SEGMENTS parameter, 634
MB (megabyte), 30
measurement conversions, 28
binary storage units, 29-30
powers of 10, 29
Media Server, 18
megabyte (MB), 30
memory
architecture, 210
batch processing systems, 276
BLOB systems, 333
buffers
block buffers, 22, 608
cache, 107-108, 608
clean buffers, 22, 608
defined, 22, 608
dirty buffers, 22, 608
redo log buffers, 608
cache
defined, 22, 608
hits, 609
data warehouses, 314
decision support systems, 296
main memory, defined, 612
OLTP systems, 255
operating systems, 170, 589
NetWare, 179-181
OS/2, 188-189
UNIX, 192-196, 531
Windows 3.11, 528
Windows 95, 530
Windows for Workgroups 3.11, 528
Windows NT, 185-186, 527
physical memory, defined, 24, 614
private PL/SQL areas, 100
private SQL areas, 100
PSE (Page Size Extension) memory, 170
SGA
defined, 616
shared pool, 582
shared memory, 8
PGA (Program Global Area), 10
SGA (System Global Area), 9-10
shared pool, 100
cache miss, 101
data dictionary cache, 103-104
library cache, 100-102
shared session, 104-106
sorts, 135-136
storage capacity, 98
tuning operating system, 99
virtual memory
defined, 25, 617
paging, 211
swapping, 211
methodologies (performance tuning), 44
analyzing results, 50
defined, 41
determining solution to problems, 48
testing solutions, 49
examining systems for problems, 45-46
finding cause of performance problems, 47-48
goal setting for optimal performance, 48-50
problem-solving methodology, flowchart, 604
microkernel architecture
defined, 183, 612
Windows NT, 183-184
middleware
defined, 556
three-tiered system architecture, 557-558
tuning, 559
TM (Transaction Monitors)
client-side code, 560
defined, 559-561
server-side code, 560
tuning operating systems for, 561
two-tiered system architecture, 556
migrating rows, 117-118
SQL statement, 599
Mission Control, 496-497
MLS_LABEL_FORMAT parameter, 643
modes, current mode, 610
monitoring systems
buying performance monitoring tools, 79
third-party tools, 78
real-time monitors, 79
threshold monitors, 79-80
tools, 74-75, 75
EXPLAIN PLAN command, 77
operating systems, 77-78
Server Manager, 76
SNMP agents, 76
SQL Trace, 76-77
SQL*DBA Monitor, 76
MPP (Massively Parallel Processor), 209-210, 612
MTS_DISPATCHERS parameter, 637
MTS_LISTENER_ADDRESS parameter, 637
MTS_MAX_DISPATCHERS parameter, 637
MTS_MAX_SERVERS parameter, 637
MTS_SERVERS parameter, 638
MTS_SERVICE parameter, 638
multiblock reads, 152, 586
batch processing systems, 275
BLOB systems, 332
data warehouses, 313
decision support systems, 295
financial systems, 372
TextServer 3.0 systems, 381
WebServer systems, 389
multiblock writes, 152-153, 586
financial systems, 372
multimedia systems, performance criteria, 39
multiprocessor systems, 209
defined, 612
MPP (Massively Parallel Processor), 209-210, 612
SMP (Symmetric Multiprocessor), 209, 615
multithreaded servers, list of parameters, 637-638

N

National Language Support, list of parameters, 643-644
NetWare, 178
architecture, 178-179
I/O subsystem, 182-183
master/slave mode, 179
memory, 179
reducing unnecessary memory usage, 179-180
SGA tuning, 180
user capacity, 180-181
networks, 181
SPX/IPX, 181-182
TCP/IP, 182
tuning, 574-575
summary of tuning guidelines, 182-183
network frames, see frames
Network Interface Cards (NICs), 613
network packets, 613
see also frames
networks
backup process enhancements, 360
bandwidth, defined, 608
batch processing systems, 276
enhancements, 280
BLOB system enhancements, 335
client, bottlenecks, 523
collisions, 609
data warehouse enhancements, 319
decision support system enhancements, 300
design considerations, 576
bandwidth, 576-577
bridges, 577
hubs, 577
routers, 577
segmenting networks, 577
design stage considerations, 84
Ethernet, 611
hardware components, 566
Ethernet networks, 566-568
fiber optic networks, 569-570
new technologies, 570
Token Ring networks, 568-569
OLTP systems, 255
enhancements, 259
operating systems
NetWare, 181-182, 574-575
OS/2, 190 575
UNIX, 196-197, 531, 575
Windows 3.11, 528-529
Windows 95, 530
Windows for Workgroups 3.11, 528-529
Windows NT, 186, 575
packets, testing size for backup process, 364
performance criteria, client-server systems, 35
protocols, 570
SPX/IPX, 571
TCP/IP, 571
replicated system enhancements, 377
software tuning, 574-576
Token Ring, 617
tuning considerations, 85-86
summary, 593
New-Order transaction (TPC-C benchmark), 61
nibbles, 29
NICs (Network Interface Cards), 613
NLS_CURRENCY parameter, 643
NLS_DATE_FORMAT parameter, 643
NLS_DATE_LANGUAGE parameter, 643
NLS_ISO_CURRENCY parameter, 643
NLS_LANGUAGE parameter, 644
NLS_NUMERIC_CHARACTERS parameter, 644
NLS_SORT parameter, 644
NLS_TERRITORY parameter, 644
No Data Protection mode (fault-tolerance), 233
NOAUDIT command, 472-473
NOCACHE hint, 486
nonequijoins, 613
nonunique indexes, 149, 427
NOPARALLEL hint, 486
normal checkpoints, 26, 133
NOT NULL constraint, 465

O

objects, schema objects, 6, 25, 615
Objects for OLE: (development tool), 17
OCI (Oracle Call Interface), defined, 613
Office Server system, defined, 368
office system
archive logs, 385
block buffers, 384
characteristics, 383
checkpoints, 385
contention, 385
defined, 382-383
design considerations, 384
library cache, 384
spin counts, 385
summary of, 385-386
tuning considerations, 384-385
offline, 613
offline backups, see cold backups
OLTP (OnLine Transaction Processing)
archive logs, 255
block buffers, 254
characteristics of system, 246
data access patterns, 246-247
goals of optimally tuned system, 248-249
loads, 247-248
summary, 249
checkpoints, 255
comparisons to batch processing systems, 265
contention
latch contention, 255
rollback, 254
defined, 27, 590, 613
design considerations, 249-250
enhancements, 256-257, 590
benchmarks, 262
clusters, 256-257
CPU, 257, 257-258
Etherplex board, 259
hash clusters, 256
I/O enhancements, 258-259
indexes, 256
load testing, 260-261
network enhancements, 259
Parallel Server option, 257
testing operating system, 261-262
testing RDBMS, 261
Transaction Monitors, 259-260
verifying performance, 260-261
hardware considerations, 253
library cache, 254
physical data layout, 250
disk arrays, 252-253
traditional disks, 250-252
rollback segments, 128
transactions, 65
tuning considerations, 253
Oracle, 254-255
server operating system, 255-256
online, 613
online tablespace backups, see hot backups
OPEN_CURSORS parameter, 254, 634
OPEN_LINKS parameter, 635
OPEN_MOUNTS parameter, 643
operating systems
asynchronous I/O, 589
batch processing systems
testing, 282, 363-365
tuning considerations, 276-277
BLOB systems
testing, 336
tuning considerations, 333
bottlenecks, finding, 93
data warehouses
testing, 320
tuning considerations, 314-315
decision support systems, 295-296
testing, 301
I/O, 170-172
memory, 170
microkernel, 612
monitoring performance, tools, 77-78
NetWare, 178
architecture, 178-179
I/O subsystem, 182-183
master/slave mode, 179
memory, 179-181
networks, 181-182
summary of tuning guidelines, 182-183
new features
cache affinity, 174, 589
post-wait semaphore, 173, 589
preemption, 173-174
scheduling processes, 173-174
OLTP systems, tuning considerations, 255-256
Oracle, network tuning, 575-576
OS/2, 188
architecture, 188
I/O subsystem, 190-191
memory, 188-189
networks, 190, 575
summary of tuning guidelines, 190-191
processes, 169
scheduling parameters, 589
striping, 113-114
tuning considerations, 94, 99
goals of optimally tuned systems, 168-169, 589
UNIX, 530-531
architecture, 192
benchmarking new features, 200-203
development of, 191
disabling preemptive scheduling, 201
I/O subsystem, 197-200
ISM (Intimate Shared Memory), 201-203
load balancing, 202
memory, 192-196, 531
networks, 196-197, 531, 575
new features, 201-203
post-wait semaphores, 201-203
Windows 3.1
memory, 528
networks, 528-529
Windows 95
32-bit support, 529-530
memory, 530
networks, 530
Oracle support, 530
Windows for Workgroups 3.11
memory, 528
networks, 528-529
Windows NT, 183
16-bit applications, 527
architecture, 183-184
defined, 527
I/O performance, 528
I/O subsystem, 186-187
memory, 185-186, 527
networks, 186, 575
summary of tuning guidelines, 187
threads, 184
optimal backups, enhancements, 591
optimization techniques
ANALYZE command, 441-442
checking structural integrity, 444
data dictionary statistics, 445-447
gathering chained-row statistics, 444-445
gathering statistics, 442-443
choosing, 438-439
cost-based approach, 434, 441, 610
defined, 433, 438-440, 613
flowcharts (Oracle optimizer), 605
hints, 447, 478
ALL_ROWS hint, 479
CHOOSE hint, 479
FIRST_ROWS hint, 479-480
RULE hint, 480-481
rule-based approach, 433-434, 440, 614
OPTIMIZER_MODE parameter, 623
Oracle, network tuning, 575-576
Oracle Call Interface (OCI), 613
Oracle Corporation, 3
Oracle Financials, see financial systems
Oracle Mission Control, 496-497
Oracle Office, 17-18
Oracle Office Server, see Office Server system
Oracle optimizer, flowchart, 605
Oracle Power Objects, 495
see also Power Objects
Oracle TextServer 3.0, see TextServer 3.0 system
Oracle Trace, 497
Oracle WebServer, see WebServer system
Order-Status transaction (TPC-C benchmark), 62
ORDERED hint, 484
OS/2, 188
architecture, 188
I/O subsystem, 190-191
memory, 188
reducing unnecessary memory usage, 189
SGA tuning, 189
user capacity, 189
networks, 190
tuning, 575
summary of tuning guidelines, 190-191
OS_AUTHENT_PREFIX parameter, 642
OS_ROLES parameter, 642
outer joins, 505, 507, 613
overhead, I/O overhead, 117
chaining rows, 117-118
checking for recursive calls, 118-119
dynamic extensions, 118-119
migrating rows, 117-118
PCTFREE command option, 119-122
PCTUSED command option, 119-122
reduction techniques, 122

P

packages, 432-433, 457-458
advantages, 458
body, 458
declarations, 458
defined, 24, 450, 613
program units, defined, 614
RDBMS_ALERT package, 470
RDBMS_OUTPUT package, 456
syntax, 458
see also functions; procedures
packets (network packets), 613
Ethernet, 567
testing for backup process, 364
Page Size Extension (PSE) memory, 170
paging, 24, 211, 613
see also swapping
Parallel Cache Management, see PCM
PARALLEL hint, 485-486
Parallel Query option (add-on), 586-587, 613
batch processing systems, 277-278
BLOB systems, 333
data warehouses, 316
decision support systems, 297
defined, 15, 153
Direct Write Sorts option, 158
financial systems, 372
hints
CACHE, 485
NOCACHE, 486
NOPARALLEL, 486
PARALLEL, 485-486
PUSH_SUBQ, 486
parallel index creation, 159
parallel loading, 159
parallel query operation, 153-155
degree of parallelism, 156-158
I/O configuration, 156
parallel recovery, 160-161
parameters, list of, 626-627
SQL statement processing, 401
TextServer 3.0 systems, 381
WebServer systems, 389
Parallel Server Lock processes (LCK), 12
Parallel Server option (add-on), 161-164, 587-588, 613
architecture, 340-343
batch processing systems, 278
clusters, 339, 340
data warehouses, 316-317
decision support systems, 297-298
defined, 14, 339
design considerations, 343
goals, 343-345
enhancements, 590
locks, 162-163, 342
DLM (Distributed Lock Manager), 341
PCM (Parallel Cache Management), 342-343
OLTP enhancement, 257
parameters, 346-347
list of, 639-641
server interconnect, 162
system design, 346
WebServer systems, 389
PARALLEL_DEFAULT_ MAX_INSTANCES parameter, 347, 641
PARALLEL_DEFAULT_ MAX_SCANS parameter, 587, 626
PARALLEL_DEFAULT_ SCANSIZE parameter, 587, 627
PARALLEL_MAX_SERVERS parameter, 587, 627
PARALLEL_MIN_SERVERS parameter, 627
PARALLEL_SERVER_IDLE_ TIME parameter, 627
parameters
analysis tools, list of parameters, 627-629
AUDIT_TRAIL, 641
AUTO_MOUNTING, 642
BACKGROUND_DUMP_ DEST, 629
BLANK_TRIMMING, 629
CACHE_SIZE_THRESHOLD, 639
CHECKPOINT_PROCESS, 629
CLEANUP_ROLLBACK_ ENTRIES, 630
CLOSE_CACHED_OPEN_ CURSORS, 630
COMMIT_POINT_STRENGTH, 638
COMPATABLE, 630
COMPATIBLE_NO_ RECOVERY, 630
CONTROL_FILES, 630
CREATE SEQUENCE command, 502-503
DB_BLOCK_BUFFERS, 620
DB_BLOCK_CHECKPOINT_ BATCH, 620-621
DB_BLOCK_CHECKSUM, 628
DB_BLOCK_LRU_ EXTENDED_ STATISTICS, 628
DB_BLOCK_LRU_ STATISTICS, 628
DB_BLOCK_SIZE, 621
DB_DOMAIN, 630
DB_FILE_MULTIBLOCK_ READ_COUNT, 621
DB_FILE_SIMULTANEOUS_ WRITES, 621
DB_FILES, 631
DB_LOG_CHECKSUM, 628
DB_MOUNT_MODE, 642
DB_NAME, 631
DBLINK_ENCRYPT_LOGIN, 631
DISCRETE_TRANSACTIONS_ ENABLED, 621
distributed option, list of parameters, 638-639
DISTRIBUTED_LOCK_ TIME-OUT, 638
DISTRIBUTED_RECOVERY_ CONNECTION_ HOLD_TIME, 638
DISTRIBUTED_ TRANSACTIONS, 639
DML_LOCKS, 622
ENQUEUE_RESOURCES, 631
EVENT, 628
FIXED_DATE, 628
functions, 454
GC_DB_LOCKS, 639
GC_FILES_TO_LOCKS, 640
GC_LCK_PROCS, 640
GC_ROLLBACK_LOCKS, 640
GC_ROLLBACK_ SEGMENTS, 640
GC_SAVE_ROLLBACK_ LOCKS, 640
GC_SEGMENTS, 640
GC_TABLESPACES, 640
general, list of parameters, 629-637
GLOBAL_NAMES, 632
IFILE, 632
INIT_SQL_FILES, 632
initialization para- meters, 612
INSTANCE_NUMBER, 641
JOB_QUEUE_INTERVAL, 632
JOB_QUEUE_KEEP_ CONNECTIONS, 632
JOB_QUEUE_PROCESSES, 632
LABEL_CACHE_SIZE, 642
LICENSE_MAX_SESSIONS, 632
LICENSE_MAX_USERS, 633
LICENSE_SESSIONS_ WARNING, 633
LOG_ARCHIVE_BUFFER_ SIZE, 622
LOG_ARCHIVE_BUFFERS, 622
LOG_ARCHIVE_DEST, 633
LOG_ARCHIVE_FORMAT, 633
LOG_ARCHIVE_START, 634
LOG_BUFFER, 622
LOG_CHECKPOINT_ TIMEOUT, 623
LOG_CHECKPOINTS_TO_ ALERT, 634
LOG_CHECPOINT_ INTERVAL, 622
LOG_FILES, 634
LOG_SIMULTANEOUS_ COPIES, 623
LOG_SMALL_ENTRY_ MAX_SIZE, 623
MAX_COMMIT_ PROPAGATION_ DELAY, 641
MAX_DUMP_FILE_SIZE, 634
MAX_ENABLED_ROLES, 634
MAX_ROLLBACK_ SEGMENTS, 634
MLS_LABEL_FORMAT, 643
MTS_DISPATCHERS, 637
MTS_LISTENER_ADDRESS, 637
MTS_MAX_DISPATCHERS, 637
MTS_MAX_SERVERS, 637
MTS_SERVERS, 638
MTS_SERVICE, 638
multithreaded servers, list of parameters, 637-638
National Language Support, list of parameters, 643, 644
NLS_CURRENCY, 643
NLS_DATE_FORMAT, 643
NLS_DATE_LANGUAGE, 643
NLS_ISO_CURRENCY, 643
NLS_LANGUAGE, 644
NLS_NUMERIC_ CHARACTERS, 644
NLS_SORT, 644
NLS_TERRITORY, 644
OLTP systems, 254
OPEN_CURSORS, 634
OPEN_LINKS, 635
OPEN_MOUNTS, 643
OPTIMIZER_MODE, 623
OS_AUTHENT_PREFIX, 642
OS_ROLES, 642
Parallel Query option, list of parameters, 626-627
Parallel Server option, list of parameters, 639-641
PARALLEL_DEFAULT_ MAX_INSTANCES, 641
PARALLEL_DEFAULT_ MAX_SCANS, 626
PARALLEL_DEFAULT_ SCAN-SIZE, 627
PARALLEL_MAX_SERVERS, 627
PARALLEL_MIN_SERVERS, 627
PARALLEL_SERVER_ IDLE_TIME, 627
performance, list of parameters, 620-626
PRE_PAGE_SGA, 623
PROCESSES, 635
RECOVERY_PARALLELISM, 627
REMOTE_LOGIN_ PASSWORDFILE, 635
REMOTE_OS_AUTHENT, 639
REMOTE_OS_ROLES, 639
ROLLBACK_SEGMENTS, 624
ROW_CACHE_CURSORS, 624
ROW_LOCKING, 624
SCO UNIX, asynchronous I/O, 199
security, list of parameters, 641-642
SEQUENCE_CACHE_ ENTRIES, 624
SEQUENCE_CACHE_HASH_ BUCKETS, 624
SERIALIZABLE, 624-625
SESSION_CACHED_ CURSOR, 625
SESSIONS, 635
SHARED_POOL_SIZE, 625
SINGLE_PROCESS, 636
SMALL_TABLE_ THRESHOLD, 625
SNAPSHOT_REFRESH_ INTERVAL, 635
SNAPSHOT_REFRESH_ KEEP_CONNECTION, 635
SNAPSHOT_REFRESH_ PROCESS, 636
SORT_AREA_RETAINED_ SIZE, 625
SORT_AREA_SIZE, 626
SORT_SPACEMAP_SIZE, 626
SQL Trace, 404-405
interpreting, 409
TKPROF program, 407-409
SQL_TRACE, 629
SQL92_SECURITY, 642
TEMPORARY_TABLE_ LOCKS, 636
THREAD, 641
TIMED_STATISTICS, 629
TRANSACTIONS, 636
TRANSACTIONS_PER_ ROLLBACK_SEGMENT, 636
Trusted Oracle7 option, list of parameters, 642-643
UNIX
asynchronous I/O, 198
SHMMAX, 194
SHMSEG, 194
UnixWare, asynchronous I/O, 199
USER_DUMP_DEST, 637
parsing SQL statements, 399-400
Partial Data Protection mode (fault-tolerance), 234-235
Patrol tool, 498-499
Payment transaction (TPC-C benchmark), 62
PCM locks (Parallel Cache Management), 162
Parallel Server option, 342-343
PCTFREE command option, 119-122
PCTUSED command option, 119-122
performance criteria
batch-processing systems, 38
checklist, 39
client/server systems, 32-33
checklist, 35-36
client component, 33
network, 35
server component, 33-35
determing problems, 46
multimedia systems, 39
terminal-based systems (host-based), 36
checklist, 37-38
database, 36-37
front-end applications, 36
performance monitoring tools, 73-75
buying, 79
EXPLAIN PLAN command, 77
operating systems, 77-78
Server Manager, 76
SNMP agents, 76
SQL Trace, 76-77
SQL*DBA Monitor, 76
third-party tools, 78-79
real-time monitors, 79
threshold monitors, 79-80
performance problems
finding, 92-93
methodology
analyzing results, 50
examing systems for, 45-46
finding cause of, 47-48
goal setting for optimal performance, 48-50
solutions for, 48-49
persistent areas (private SQL area), 100
Personal Oracle for Windows, 16
PGA, 10
physical data layout
batch processing systems, 270-271
disk arrays, 272-273
traditional disks, 271-272
BLOB systems
disk arrays, 329-331
traditional disks, 328-329
data warehouses, 308-309
disk arrays, 310-311
traditional disks, 309-310
decision support systems
disk arrays, 292-293
traditional disks, 291-292
OLTP (OnLine Transaction Processing), 250
disk arrays, 252-253
traditional disks, 250-252
physical layer
control files, 5
data files, 5
defined, 4
redo log files, 5
physical memory, 24, 614
PL/SQL
defined, 14, 614
functions, 24, 454-455
private PL/SQL areas, 100
procedures, 454-455
properties, 454-455
PL/SQL Interpreter (Procedure Builder tool), 542
platters (disk drive), 214
cylinders, 215
tracks, 214
PMON (Process Monitor), 11
post-wait semaphore feature, 173
operating systems, 589
UNIX, 201
Power Objects (development tool), 17, 495
classes, 545-546
features, 544-546
Forms Designer tool, 544-545
PowerBuilder, 495, 552
PRE_PAGE_SGA parameter, 623
Precise/SQL suite, 498
preempting processes, 173-174
preemptive scheduling, UNIX
cache affinity, 202
disabling, 201
PRIMARY constraint, 465
primary keys, 614
private SQL area (memory), 10
persistent areas, 100
runtime areas, 100
problem-solving methodology, flowchart, 604
Procecural option, see PL/SQL
Procedure Builder tool (Designer/2000), 537, 542-544
procedures, 432-433
body, 453
comparisons to functions, 452
declarations, 453
defined, 24, 452-453, 614
EXCEPTION, 455
packages, 24
PL/SQL language, 454-455
RDBMS_OUTPUT package, 456
RDBMS_OUTPUT.DISABLE, 456
RDBMS_OUTPUT.ENABLE, 456
RDBMS_OUTPUT.GET_LINE, 456
RDBMS_OUTPUT.GET_LINES, 456
RDBMS_OUTPUT.PUT, 456
RDBMS_OUTPUT.PUT_LINE, 456
READ_CLIENT_INFO, 417
READ_MODULE, 417
SET_ACTION, 417
SET_CLIENT_INFO, 417
SET_MODULE, 417
stored procedures
creating, 456-457
defined, 7, 452
program units, 614
properties, 452
replacing, 457
syntax, 453
see also functions
Process Modeller tool, 491
Process Monitor (PMON), 11
PROCESSES parameter, 635
processes
ARCH, 26-27
background
ARCH (Archiver), 12
CKPT (Checkpoint), 11, 26
DBWR (Database Writer), 11
Dnnn (Dispatcher), 12
LCK (Parallel Server Lock), 12
LGWR (Log Writer), 11
PMON (Process Monitor), 11
RECO (Recovery), 12
SMON (System Monitor), 12
calculating number for system, 169
defined, 10
operating systems, 169
prioritizing, 169
scheduling, 173-174
server (shadow), 11
user (client), 10
see also lightweight processes; threads; traditional processes
processing
array processing, 510
queries, 400
SQL statements, 397
bind variables, 401
cursor creation, 398
executing statements, 401
fetching returned rows, 401-402
parallelization, 401
parsing statement, 399-400
query processing, 400
summary, 402
transactions, 395-396
processors
CPU
backup process enhancements, 359
batch processing system enhancements, 279
BLOB system enhancements, 334
cache, 210
CISC (Complex Instruction Set Computer), 207-208
data warehouse enhancements, 317
decision support system enhancements, 298-299
OLTP enhancements, 257-258
replicated system enhancements, 377
RISC (Reduced Instruction Set Computer), 207-208
multiprocessor systems, 209
MPP (Massively Parallel Processor), 209-210
SMP (Symmetric Multiprocessor), 209
products
applications, 17
ConText, 18
Media Server, 18
Oracle Financial Applications, 17
Oracle Office, 17-18
Oracle WebServer, 18
development tools, 16-17
Oracle RDBMS, 13
Distributed option, 14
Parallel Query option, 15
Parallel Server option, 14
PL/SQL, 14
Server Manager, 15
SQL*Net, 14
Symmetric Replication, 15
Trusted Oracle, 15
Personal Oracle for Windows, 16
services
consulting, 18
education, 19
support, 19
Workgroup Server, 15-16
Program Unit Editor (Procedure Builder tool), 542
program units, 24, 614
protocols, 570-571
SPX/IPX, 571, 615
TCP/IP (Transmission Control Protocol/Internet Protocol), 571, 616
PSE (Page Size Extension) memory, 170
PUSH_SUBQ hint, 486

Q

queries
ad-hoc, 22, 608
business support systems, 611
component queries, 609
compound queries, 609
decision support systems, 28
defined, 614
joins
defined, 612
join conditions, 612
nonequijoins, 613
outer joins, 613
self joins, 615
Parallel Query option, 153
degree of parallelism, 156-158
Direct Write Sorts option, 158
I/O configuration, 156
parallel index creation, 159
parallel loading, 159
parallel query operation, 153-158
parallel recovery, 160-161
processing, 400
rollback segments, 128
subqueries, 609, 616
query parameter, SQL Trace, 409
queueing, 218

R

RAID (Redundant Array of Inexpensive Disks), 229
comparing RAID levels, 240-241
configuration issues, 235-236
distributing random I/Os, 237-238
isolating sequential I/Os, 236-237
sizing logical volume, 238-239
RAID-0, 230
RAID-1, 230-231
RAID-2, 231
RAID-3, 231
RAID-4, 232
RAID-5, 232
summarizing RAID levels, 232
RAM (Random Access Memory), 612
random I/Os, 220
defined, 24, 111, 598, 614
disk arrays, distributing random I/Os, 237-238
disk rates, 598
spreading out, 590, 598
striping data, 112-113
hardware, 114-115
operating system, 113-114
options, 115-116
raw device interface, UNIX, 198
RDBMS (Relational Database Management Systems)
databases, 4
defined, 4
instance, 4
products, 13
Distributed option, 14
Parallel Query option, 15
Parallel Server option, 14
PL/SQL, 14
Server Manager, 15
SQL*Net, 14
Symmetric Replication, 15
Trusted Oracle, 15
RDBMS_ALERT package, 470
RDBMS_OUTPUT package, 456
RDBMS_OUTPUT.DISABLE procedure, 456
RDBMS_OUTPUT.ENABLE procedure, 456
RDBMS_OUTPUT.GET_LINE procedure, 456
RDBMS_OUTPUT.PUT procedure, 456
RDBMS_OUTPUT.PUT_LINE procedure, 456
read consistency, 24, 614
Read Lock mode (Parallel Server option), 342
read-only snapshots, 374
read/write caches, disk arrays, 229
READ_CLIENT_INFO procedure, 417
READ_MODULE procedure, 417
real-time monitoring tools, 79
recovery process (RECO), 12, 351-352
RECOVERY_PARALLELISM parameter, 587, 627
recursive calls
checking, 118
defined, 118, 614
SQL statement, 599
redo log buffer, 10, 608
contention
latch contention, 131-132
SQL statement, 600-601
redo log files, 5, 26-27, 614
Reduced Instruction Set Computer, see RISC processors
Redundant Array of Inexpensive Disks, see RAID
referential integrity
defined, 462, 614
tables, 463-464
registering applications, 417
Relational Database Management System, see RDBMS
remote transactions, distributed systems, 378
REMOTE_LOGIN_ PASSWORDFILE parameter, 635
REMOTE_OS_AUTHENT parameter, 639
REMOTE_OS_ROLES parameter, 639
replicated systems
block buffers, 376
characteristics, 374-375
contention, 376
defined, 368, 374
design considerations, 375-377
goals of optimally tuned systems, 375
library cache, 376
read-only snapshots, 374
snapshots, 615
summary of, 377
symmetric replication, 15, 374
updatable, 374
replication, 614
Reports Designer tool
Designer/2000, 536, 538-540
Oracle Power Objects, 545
Reports Generator tool, 492
ReportSmith, 548-549
response time
analyzing problems, 47-48
defined, 42
goals, 42-43
performance criteria, terminal-based systems, 37
RETURN function, 454, 455
RISC processors (Reduced Instruction Set Computer), 208
rollback contention
defined, 123-126, 130, 614
financial systems, 371
office systems, 385
OLTP systems, 254
replicated systems, 376
SQL statements, 599-600
WebServer systems, 389
rollback segments
avoiding dynamic growth of, 129
batch processing systems, 275
BLOB systems, 332
data warehouses, 313
decision support systems, 295
defined, 8, 614
dynamic growth of, 600
extent size and number, 128-129
financial systems, 371
number of, 127-128
size, 128
TextServer 3.0 systems, 381
transaction tables, 124
ROLLBACK_SEGMENTS parameter, 624
rotational latency, 217
averages, 219
routers, network design considerations, 577
row cache, see data dictionary, cache
row-level locking, 508
ROW_CACHE_CURSORS parameter, 624
ROW_LOCKING parameter, 624
ROWID hint, 483
rows
chaining, 117-118
SQL statement, 599
migrating, 117-118
SQL statement, 599
rows parameter, SQL Trace, 409
RULE hint, 480-481
rule-based optimization approach, 433-434, 440
choosing, 438-439
defined, 614
hints, 447
runtime areas (private SQL area), 100

S

S-Designor tool, 493
scalability, 24, 615
Scheduler
batch processing systems, 276
BLOB systems, 333
data warehouses, 314
decision support systems, 296
OLTP systems, 255
scheduling
operating systems
parameters, 589
UNIX options, 201-203
processes, 173-174
schemas
defined, 6-7, 25, 615
objects, 6, 25, 615
SCO UNIX
buffer cache, tuning, 193
networks, 196-197
parameters
asynchronous I/O, 199
shared memory, 195
scripts, CD-ROM, 646-648
searches, see TextServer 3.0 systems
security, list of parameters, 641-642
seek time, 216-217
averages, 219
segments
backups, 360-362
data segments, 8
defined, 7, 118, 615
index segments, 8
networks, 577
rollback segments, 123-126
avoiding dynamic growth of, 129
batch processing systems, 275
BLOB systems, 332
contention, 123-130
data warehouses, 313
decision support systems, 295
defined, 8
dynamic growth of, 600
extent size and number, 128-129
financial systems, 371
number of, 127-128
optimizing, 130
size, 128
SQL statement, 599-600
TextServer 3.0 systems, 381
transaction tables, 124
temporary segments, 8
see also data blocks; extents
SELECT statement, displaying execution plan, 414-416
SELECT…FOR UPDATE statement, 509
self joins, 505-506, 615
SEQUENCE_CACHE_ENTRIES parameter, 624
SEQUENCE_CACHE_HASH_ BUCKETS parameter, 624
sequences
defined, 615
program units, 614
table sequences
creating, 502-503
defined, 502
generating primary key values, 504-505
generating sequence values, 503-504
summary, 504-505
tuning considerations, 503
sequential I/O
defined, 25, 111, 220-222, 598, 615
disk arrays, isolating sequential I/Os, 236-237
disk rates, 598
isolating, 112, 590, 598
serial reads, 473
joins, 508-509
SERIALIZABLE parameter, 624-625
Server Generator tool, 492
server interconnect, 162
Server Manager, 15, 76, 615
server processes (shadow processes), 11
servers
multithreaded servers, parameters, 637-638
performance criteria, 33-35
tuning performance, 85
services (Oracle)
consulting, 18
education, 19
support, 19
session information (PGA), 10
SESSION_CACHED_CURSORS parameter, 625
SESSIONS parameter, 635
sessions, 615
SET_ACTION procedure, 417
SET_CLIENT_INFO procedure, 417
SET_MODULE procedure, 417
SGA (System Global Area)
buffer cache, 9
defined, 9-10, 25, 615
redo log buffer, 10
shared pool, 10, 582
buffer cache, 583
data dictionary cache, 582
library cache, 582
shared session information, 583
shadow processes, see server processes
shared memory, 8
PGA (Program Global Area), 10
SGA (System Global Area)
buffer cache, 9
redo log buffer, 10
shared pool, 10
UNIX, parameters, 194
shared pool
buffer cache, 583
cache hit ratio, 107-108
cache miss, 101
data dictionary cache, 103-104, 582
defined, 10, 100, 582
library cache, 100-102, 582
increasing cache hits, 100-102
shared session area, 104-106, 583
SHARED_POOL_SIZE parameter, 625
shareware, middleware
defined, 556
three-tiered system, 557-559
two-tiered system architecture, 556
shipping industry systems, performance criteria, 39
SHMMAX parameter, 194
SHMSEG parameter, 194
Simple Network Management Protocol (SNMP), agents, 76
simple statements, 615
SINGLE_PROCESS parameter, 636
SMALL_TABLE_THRESHOLD parameter, 625
SMON (System Monitor), 12
SMP (Symmetric Multiprocessor), 209, 615
SNAPSHOT_REFRESH_ INTERVAL parameter, 635
SNAPSHOT_REFRESH_KEEP_ CONNECTION para-meter, 635
SNAPSHOT_REFRESH_ PROCESS parameter, 636
snapshots
defined, 615
read-only snapshots, 374
updatable snapshots, 374
SNMP (Simple Network Management Protocol), agents, 76
software
backup process
cold (offline) backups, 355
hot (online) backups, 357-358
testing, 364-365
networks, 574
NetWare, 574-575
Oracle tuning, 575-576
OS/2, 575
UNIX, 575
Windows NT, 575
software disk arrays, 227
Solaris
buffer cache, tuning, 193
networks, 197
shared memory, parameters, 194-195
SORT_AREA_RETAINED_SIZE parameter, 625
SORT_AREA_SIZE parameter, 626
SORT_SPACEMAP_SIZE parameter, 626
sorts
direct write sorts, 143, 158
batch processing systems, 277
data warehouses, 315
decision support systems, 296
memory, 135-136
SQL statement, 601-602
Source Code Control (Procedure Builder tool), 542
spin counts, 164, 588
financial systems, 371
office systems, 385
WebServer systems, 388
SPX/IPX protocol
defined, 571, 615
operating systems, NetWare, 181-182
SQL (Structured Query Language)
cursors, 610
queries
defined, 614
joins, 612
subqueries, 609, 616
scripts on CD-ROM, 646-648
statements
analyzing effects of, 424-425
analyzing with EXPLAIN PLAN command, 424
analyzing with SQL Trace, 423-424
buffer cache, 597
chained rows, 599
characteristics of optimized statements, 394
complex statements, 609
data dictionary cache, 596-597
determining dynamic rollback growth, 600
executing, 401
flowchart of statement processing, 605
free list contention, 602
functions, 24
hints, 394
identifying badly formed statements, 394-395
I/O usage, 597-598
latch contention, 601
library cache, 596
migrated rows, 599
packages, 394
procedures, 24, 394, 614
processing, 397-402
recursive calls, 599, 614
redo log buffer contention, 600-601
rollback contention, 599-600
simple statements, 615
sort performance, 601-602
transaction processing, 395-396
tuning, 422-424
UPDATE statement, 617
tuning private SQL areas, 100
SQL Coder tool, 493-494
SQL DBA, see Server Manager
SQL Trace feature
anaylyzing SQL statements, 423-424
defined, 76-77, 404
initialization parameters, 404-405
disabling
command, 410
for an instance, 406
per-session basis, 405-406
elapsed time, 412
enabling
command, 409
for an instance, 406
per-session basis, 405
functionality, 406
interpreting information, 409-414
library cache statistics, 413
summary of, 414
TKPROF program
functionality, 407-409
output file, 410-414
parameters, 407-409
SQL Windows, 495, 550-552
SQL*DBA Monitor, 76
SQL*Loader, 616
SQL*Net, 14, 616
SQL*Plus, 616
SQL/DBA, 616
SQL_TRACE parameter, 629
SQL92_SECURITY parameter, 642
stack space (PGA), 10
standard benchmarks
batch processing systems, 282-283
BLOB systems, 336-337
data warehouses, 320-321
decision support systems, 301-302
defined, 51-53
OLTP systems, 262
TPC (Transaction Processing Performance Council), 53-54
ACID tests (Atomicity, Consistency, Isolation, and Durability), 55-58
interpreting spreadsheet of results, 67-69
members, 53-54
publishing benchmarks, 57-58
results of benchmarks, 56-58, 67-69
rules, 55
steering committee, 54
Technical Advisory Board (TAB), 54
TPC-A benchmark, 59-60
TPC-B benchmark, 60-61
TPC-C benchmark, 61-63
TPC-C/S (client/server) benchmark, 66-67
TPC-D benchmark, 63-64
TPC-E benchmark, 64-66
statements
anaylyzing effects of, 424-425
cache
buffer cache, 597
data dictionary cache, 596-597
library cache, 596
characteristics of optimized, 394
complex, 609
contention
free list, 602
latch, 601
redo log buffer, 600-601
rollback, 599-600
DELETE, 611
determining dynamic rollback growth, 600
EXPLAIN PLAN
analyzing statement execution, 424
defined, 414
extracting results of, 416
initialization, 414-415
invoking, 415-416
flowchart of statement processing, 605
functions, 24
hints, 394
errors, 477-478
examples, 477-478
implementing, 476-478
multiple, 478
optimization techniques, 478-481
syntax, 477
identifying badly formed statements, 394-395
INSERT, 612
I/O usage, 597-598
joins, equijoins, 611
optimizing, 438
packages, 394
procedures, 24, 394, 614
processing, 397
bind variables, 401
cursor creation, 398
executing statements, 401
fetching returned rows, 401-402
parallelization, 401
query processing, 400
statement parsing, 399-400
summary, 402
recursive calls, 599, 614
rows, chained or migrated, 599
simple statements, 615
sort performance, 601-602
SQL Trace feature
analyzing statements, 423-424
defined, 404
disabling, 405-406, 410
elapsed time, 412
enabling, 405-406, 409
functionality, 406
initialization parameters, 404-405
interpreting information, 409-414
library cache statistics, 413
summary, 414
TKPROF output file, 410-414
TKPROF program, 407-409
transaction processing, 395-396
tuning, 422-424
UPDATE, 617
steering committee (TPC), 54
Stock-Level transaction (TPC-C benchmark), 62
stored functions
creating, 456-457
defined, 452
properties, 452
replacing, 457
see also functions
stored procedures
creating, 456-457
defined, 7, 452
program units, 614
properties, 452
replacing, 457
see also procedures
Stored Program Unit Editor (Procedure Builder tool), 542
storing tables, see clusters
streaming, 616
stripes, 226
striping data
defined, 112
hardware, 114-115
operating system, 113-114
options, 115-116
Oracle, 112-113
striping factor, 226
Structured Query Language, see SQL
subnetting networks, 576
subqueries, 609, 616
suites, Oracle Office, 17-18
support services, 19
swapping, 25, 211, 616
see also paging
Symmetric Multiprocessor, see SMP
Symmetric Replication, 15, 374
synchronous I/O, 171-172
synonyms, 616
syntax
functions, 453
hints, 477
packages, 458
procedures, 453
triggers, 470
System Global Area, see SGA
system memory
architecture, 210
virtual memory, 211
defined, 210
speed, 206
System Monitor, see SMON
SYSTEM tablespace, 5
systems, limitations, 95
Systems Designer tool, 491-492
Systems Modeller tool, 491

T

table sequences
creating, 502-503
defined, 502
generating primary key values, 504-505
generating sequence values, 503-504
summary, 504-505
tuning considerations, 503
table-level locking, 508
tables
Cartesian products, 609
clusters, 6, 609
columns, 6
data dictionary, 610
defined, 6, 616
dynamic performance tables
defined, 24, 611
views (table of), 74-75
indexes, choosing tables to index, 427-428
integrity, referential, 463-464
transaction tables, rollback segments, 124
V$ROLLSTAT, rollback segment data, 129
V$ROWCACHE, data dictionary statistics, 103
V$SYSSTAT, buffer cache statistics, 107
V$WAITSTAT, rollback segment data, 127
views, data in, 446
tablespaces
data files, 5-6
defined, 5-6, 616
fragmentation, 144-146
SYSTEM tablespace, 5-6
tape drive, testing block size for backup process, 363
tapes, streaming, 616
TB (terabyte), 30
TCP/IP (Transmission Control Protocol/Internet Protocol), 571
defined, 616
operating systems, NetWare, 182
temporary segments, 8
TEMPORARY_TABLE_LOCKS parameter, 636
terabyte (TB), 30
terminal-based systems (host-based systems), performance criteria, 36
checklist, 37-38
database, 36-37
front-end application, 36
testing performance, see benchmarks
testing stage, 84
clients, 85
networks, 85-86
servers, 85
TextServer 3.0 system
archive logs, 381
block buffers and size, 381
characteristics, 379-380
checkpoints, 381
clusters, 381
contention, latch contention, 381
defined, 368, 379
design considerations, 380-381
enhancements, 381-382
hash clusters, 381
indexes, 381
library cache, 381
multiblock reads, 381
Parallel Query, 381
segments, rollback, 381
summary, 382
tuning considerations, 381
third-party tools
analysis tools, 497
AdHawk, 498
DATA-XPERT, 498
DBGENERAL, 498
Patrol, 498-499
Precise/SQL suite, 498
TSreorg, 498
application development tools, 495, 546
Delphi, 495-496, 547-548
PowerBuilder, 495, 552
ReportSmith, 548-549
SQL Windows, 495, 550-552
database design tools, 492-493
ERwin/ERX, 493
S-Designor, 493
SQL Coder, 493-494
monitoring tools, 78
real-time monitors, 79
threshold monitors, 79-80
THREAD parameter, 641
threads
defined, 616
operating systems, Windows NT, 184
see also lightweight processes; processes; traditional processes
three-tiered system architecture, 557-558
threshold monitoring tools, 79-80
throughput, 42
TIMED_STATISTICS parameter, 404, 629
TKPROF program, 407-409
output file (listing), 410-414
parameters, 407-409
TM (Transaction Monitors)
client-side code, 560
defined, 559-561
OLTP enhancement, 259-260
server-side code, 560
tuning, 561
Token Ring networks, 568-569, 617
tools
analysis tools, 496
defined, 490
Oracle Mission Control, 496-497
Oracle Trace, 497
summary, 499
third-party tools, 497-499
application development tools, 494, 534-536
advantages, 534
defined, 490
Developer/2000, 495, 536-544
first-generation tools, 534-535
modern tools, 535
Oracle Power Objects, 544-546
Power Objects, 495
third-party tools, 495-496, 546-552
tuning automatically generated SQL statements, 535-536
buying performance monitoring tools, 79
database design tools
defined, 490
Designer/2000, 490-492
third-party tools, 492-494
monitoring performance, 75
EXPLAIN PLAN command, 77
Server Manager, 76
SNMP agents, 76
SQL Trace, 76-77
SQL*DBA Monitor, 76
operating systems, 77-78
third-party monitoring tools, 78-79
real-time monitors, 79
threshold monitors, 79-80
TPC (Transaction Processing Performance Council)
ACID tests (Atomicity, Consistency, Isolation, and Durability), 55-58
defined, 52-54
members, 53-54
publishing benchmarks, 57-58
results of benchmarks, 67-69
Full Disclosure Report, 56-58
interpreting spreadsheet, 67-69
rules, 55
standard benchmarks, objectives, 53
steering committee, 54
Technical Advisory Board (TAB), 54
TPC-A benchmark, 59-60
TPC-B benchmark, 60-61
TPC-C benchmark, 61-63
TPC-C/S (client/server) benchmark, 66-67
TPC-D benchmark, 63-64
TPC-E benchmark, 64-66
trace files, see SQL Trace
tracks (platters), 214
traditional disk drives
batch processing systems, 271-272
BLOB systems, 328-329
data warehouses, 309-310
decision support systems, 291-292
OLTP systems, 250-252
traditional processes, see processes
training services, 19
Transaction Monitors (TM)
client-side code, 560
defined, 559-561
OLTP enhancements, 259-260
server-side code, 560
tuning, 561
Transaction Processing Performance Council, see TPC
transaction tables, rollback segments, 124
TRANSACTIONS parameter, 636
transactions
benchmarks, TPC-C, 61-62
committed transactions, 13
completing, 12-13
defined, 12-13, 25, 617
discrete transactions, 435-436
distributed transactions, distributed systems, 378
latency, 214
local transactions, distributed systems, 378
OLTP (OnLine Transaction Processing), 65
processing, 395-396
remote transactions, distributed systems, 378
rollback segments, 123-126
user-transaction profile, flowchart, 605
TRANSACTIONS_PER_ ROLLBACK_SEGMENT parameter, 636
Transmission Control Protocol/Internet Protocol, see TCP/IP
triggers
alerts, 470
creating, 469, 470-471
defined, 25, 469, 617
event logging, 469
generating data, 469
keywords, 470
qualifying statements, 470
summary, 472
syntax, 470
validating data, 469
viewing, 471-472
see also integrity
Trusted Oracle7 option
defined, 15
parameters, list of, 642-643
TSreorg, 498
tuning
goals
connectivity, 43
fault tolerance, 43
load time, 44
response time, 42-43
throughput, 42
memory
buffer cache, 107-108
operating system, 99
private PL/SQL areas, 100
private SQL areas, 100
shared pool, 100-106
methodology, 44
analyzing results, 50
determing solution to problems, 48-49
examining system for problems, 45-46
finding cause or performance problems, 47-48
goal setting for optimal performance, 48-50
performance
clients, 85
networks, 85-86
servers, 85
Tuxedo systems, features, 344
two-phase commit, 617
two-tiered system architecture, 556

U

UNIQUE constraint, 465
unique indexes, 149, 427
units of measurements, conversions, 28
binary storage units, 29-30
powers of 10, 29
UNIX, 530-531
architecture, 192
development of, 191
I/O subsystem, 197-198
asynchronous I/O, 198-200
file system, 198
raw device interface, 198
memory, 192-193, 531
reducing unnecessary memory usage, 193
SGA tuning, 194-195
user capacity, 195-196
networks, 196, 531
SCO UNIX, 196-197
Solaris, 197
tuning, 575
UnixWare, 197
new features
benchmarking, 200
cache affinity, 202
disabling preemptive scheduling, 201
ISM (Intimate Shared Memory), 201
load balancing, 202
post-wait semaphores, 201
parameters
asynchronous I/O, 198
SHMMAX, 194
SHMSEG, 194
summary of tuning guidelines, 202-203
UNIX SVR4, 191
UnixWare
buffer cache, tuning, 193
networks, 197
parameters
asynchronous I/O, 199
shared memory, 194-195
updatable snapshots, 374
UPDATE statement
defined, 617
execution plan, displaying with EXPLAIN PLAN command, 414-416
updating rollback segments, 128
USE_CONCAT hint, 483-484
USE_MERGE hint, 484
USE_NL hint, 485
user processes (client processes), 10
user-transaction profile, flowchart, 605
USER_CONS_COLUMNS view, 467
USER_CONSTRAINTS view, 467
USER_DUMP_DEST parameter, 405, 637
USER_TRIGGERS view, 471

V

V$FILESTAT dynamic performance table, disk access information, 597
V$LATCH dynamic performance table, latch contention data, 601
V$ROLLSTAT dynamic performance table, dynamic rollback growth data, 600
V$ROLLSTAT table, rollback segment data, 129
V$ROWCACHE table, data dictionary statistics, 103
V$SYSSTAT dynamic performance table, recursive calls data, 599
V$SYSSTAT table, buffer cache statistics, 107
V$WAITSTAT dynamic performance table, rollback contention data, 599
V$WAITSTAT table, rollback segment data, 127
validating data, triggers, 469
VARCHAR2 data type, 510
variables, binding, 401
viewing
constraints, 467-469
triggers, 471-472
views
defined, 6, 617
dynamic performance tables, 74-75
virtual memory
defined, 25, 211, 617
paging, 24, 211
swapping, 25, 211

W-X-Y-Z

warehouses, see data warehouses
WebServer system
archive logs, 389
block
buffers, 388
size, 389
characteristics, 386-387
checkpoints, 389
clusters, 389
contention, latch and rollback, 389
defined, 18, 368, 386
design considerations, 387-388
enhancements, 389-390
hash clusters, 389
library cache, 388
multiblock reads, 389
Parallel Query option, 389
Parallel Server option, 389
spin counts, 388
summary, 390
tuning considerations, 388-389
Windows 3.1
memory, 528
networks, 528-529
Windows 95
32-bit support, 529-530
memory, 530
networks, 530
Oracle support, 530
Windows for Workgroups 3.11
memory, 528
networks, 528-529
Windows NT, 183
16-bit applications, 527
architecture, 183-184
defined, 527
I/O performance, 528
I/O subsystem, 186-187
memory, 185, 527
reducing unnecessary memory usage, 185
SGA tuning, 185-186
user capacity, 186
networks, 186
tuning, 575
summary of tuning guidelines, 187
threads, 184
words, 29
Workgroup Server, 15-16
write caches, disk arrays, 228-229


Table of Contents


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.